#ifndef DAOT_H
#define DAOT_H

#include <QObject>
#include <QSqlQuery>
#include <QSqlRecord>
#include <QSqlError>
#include <QThread>
#include <QLocale>

#include "genericlistmodel.h"
#include "../entity/entity.h"
#include "query.h"
#include "databaseexception.h"
#include "../utils/utils.h"

#define DEFAULT_PAGE_SIZE 20

#define TABLE_TAG "<TABLE>"
#define ID_TAG "<ID>"

#define DEFAULT_PAGE_SIZE 20
#define DEFAULT_FIND_QUERY (Query("select * from " TABLE_TAG).orderBy(ID_TAG " asc"))
#define DEFAULT_COUNT_QUERY (Query("select count( " ID_TAG " ) from " TABLE_TAG))
#define DEFAULT_ID_COL_NAME QString()


template <class T>
class DAO
{
public:

    DAO(QString tableName, unsigned int pageSize = DEFAULT_PAGE_SIZE, QString idName = DEFAULT_ID_COL_NAME, Query findQuery = DEFAULT_FIND_QUERY, Query countQuery = DEFAULT_COUNT_QUERY) {
        this->m_tableName = tableName;
        this->m_pageSize = pageSize;

        if(!idName.isEmpty()) {
            this->m_idColName = QString(idName);
        } else {
            this->m_idColName = "id_";
            this->m_idColName.append(tableName);
        }

        this->m_findQuery = findQuery;
        this->m_countQuery = countQuery;
        this->m_db = QSqlDatabase::database();
    }

    void setDatabase(QSqlDatabase db) {
        this->m_db = db;
    }

    GenericListModel* findAll(QString where = QString()) {
        Query sqlQuery = m_findQuery;
        return findAll(sqlQuery.where(where));
    }

    GenericListModel* findAll(Query sqlQuery) {
        T obj;

        if(!this->m_db.isOpenError()) {
            this->m_db.open();
        }

        QString findQuery = sqlQuery.build();
        findQuery.replace(TABLE_TAG,m_tableName);
        findQuery.replace(ID_TAG,idColName());

        QSqlQuery query;
        QList<QObject *> list;

        query.setForwardOnly(true);
        if(query.exec(findQuery)) {
            list = parseAll(query);
        } else {
            qDebug("Query Error:");
            qDebug(query.lastError().text().toUtf8());
            throw DatabaseException();
        }

        QMetaObject* metaObject = (QMetaObject*)obj.metaObject();

        GenericListModel* model = new GenericListModel( (QObject*)0, metaObject);
        model->setEntries(list);

        return model;
    }

    T* findById(long mId, QString where = QString()) {
        Query sqlQuery = m_findQuery;
        return findById(mId, sqlQuery.where(where));
    }

    T* findById(long mId, Query sqlQuery) {
        if(!this->m_db.isOpenError()) {
            this->m_db.open();
        }

        QString whereId(idColName());
        whereId.append("=");
        whereId.append(QString::number(mId));

        QString findQuery = sqlQuery.where(whereId).build();
        findQuery.replace(TABLE_TAG,m_tableName);
        findQuery.replace(ID_TAG,idColName());

        QSqlQuery query;

        QList<QObject *> list;

        query.setForwardOnly(true);
        if(!query.exec(findQuery)) {
            qDebug("Query Error:");
            qDebug(query.lastError().text().toUtf8());
            throw DatabaseException();
        }

        query.next();

        if(!query.isValid()) {
            return NULL;
        }

        return parseQuery(query);
    }

    GenericListModel* loadPage(unsigned int pageNumber, QString where = QString()) {
        Query sqlQuery = m_findQuery;
        return loadPage(pageNumber, sqlQuery.where(where));
    }

    GenericListModel* loadPage(unsigned int pageNumber, Query sqlQuery) {
        T obj;

        if(!this->m_db.isOpenError()) {
            this->m_db.open();
        }

        unsigned int offset = (pageNumber-1) * m_pageSize;

        QString pageQuery = sqlQuery.limit(m_pageSize).offset(offset).build();
        pageQuery.replace(TABLE_TAG,m_tableName);
        pageQuery.replace(ID_TAG,idColName());

        QSqlQuery query;

        QList<QObject *> list;

        query.setForwardOnly(true);
        if(query.exec(pageQuery)) {
            list = parseAll(query);
        } else {
            qDebug("Query Error:");
            qDebug(query.lastError().text().toUtf8());
            throw DatabaseException();
        }

        QMetaObject* metaObject = (QMetaObject*)obj.metaObject();

        GenericListModel* model = new GenericListModel( (QObject*)0, metaObject);
        model->setEntries(list);

        return model;
    }

    unsigned int pageSize() {
        return m_pageSize;
    }

    void setPageSize(unsigned int pageSize) {
        this->m_pageSize = pageSize;
    }

    unsigned int pageCount(QString where = QString()) {
        Query sqlQuery = m_countQuery;
        return pageCount(sqlQuery.where(where));
    }

    unsigned int pageCount(Query sqlQuery) {
        unsigned int count = 0;
        unsigned int numPages = 0;

        if(!this->m_db.isOpenError()) {
            this->m_db.open();
        }

        QString countQuery = sqlQuery.build();
        countQuery.replace(TABLE_TAG,m_tableName);
        countQuery.replace(ID_TAG,idColName());

        QSqlQuery query(countQuery);

        if(query.next()) {
            count = query.value(0).toUInt();
        } else {
            qDebug("Query Error:");
            qDebug(query.lastError().text().toUtf8());
            throw DatabaseException();
        }

        numPages = count / m_pageSize;
        if(count % m_pageSize) {
            ++numPages;
        }

        return numPages;
    }

    bool save(Entity* entity, bool saveWithId = false) {
        if(!this->m_db.isOpenError()) {
            this->m_db.open();
        }

        if(entity->mId() && !saveWithId) {
            return update(entity);
        }

        QString keys = entity->keys();
        QString values = entity->values();

        if(saveWithId) {
            keys.prepend(idColName()+",");
            values.prepend("'" + QString::number(entity->mId()) +"',");
        }

        QString strQuery = "insert into " + m_tableName + "(" + keys + ") values(" + values + ")";
        QSqlQuery query;

        query.setForwardOnly(true);
        if(!query.exec(strQuery)) {
            qDebug("Query Error:");
            qDebug(query.lastError().text().toUtf8());
            return false;
        }

        return true;
    }

    bool saveAndUpdateID(Entity* entity) {
        if(!this->m_db.isOpenError()) {
            this->m_db.open();
        }

        if(entity->mId()) {
            return update(entity);
        }

        QString keys = entity->keys();
        QString values = entity->values();

        QString strQuery = "insert into " + m_tableName + "(" + keys + ") values(" + values + ")";
        QSqlQuery query;

        if(this->m_db.driverName()=="QSQLITE") {
            this->m_db.transaction();

            if(!query.exec(strQuery)) {
                qDebug("Query Error:");
                qDebug(query.lastError().text().toUtf8());
                this->m_db.rollback();
                return false;
            }

            strQuery = "SELECT last_insert_rowid() FROM " + m_tableName;
            if(!query.exec(strQuery)) {
                qDebug("Query Error:");
                qDebug(query.lastError().text().toUtf8());
                this->m_db.rollback();
                return false;
            }

            this->m_db.commit();
        } else {
            strQuery += " returning " + idColName();

            query.setForwardOnly(true);
            if(!query.exec(strQuery)) {
                qDebug("Query Error:");
                qDebug(query.lastError().text().toUtf8());
                return false;
            }
        }


        query.next();
        entity->setmId(query.value(0).toLongLong());
        return true;
    }

    bool deleteAll(QString where = QString()) {
        QString deleteQuery="delete from "+m_tableName;

        if(!where.isEmpty()) {
            deleteQuery.append(" where ");
            deleteQuery.append(where);
        }

        QSqlQuery query;
        if(!query.exec(deleteQuery)) {
            qDebug("Query Error:");
            qDebug(query.lastError().text().toUtf8());
            return false;
        }

        return true;
    }

    bool deleteById(long mId) {
        QSqlQuery query;

        QString whereId(idColName());
        whereId.append("=");
        whereId.append(QString::number(mId));

        return deleteAll(whereId);
    }

    QString idColName() {
        return m_idColName;
    }

    QString toCSV(T* entity, QStringList fields = QStringList())
    {
        QLocale defaultLocale;
        QLocale::setDefault(QLocale::C);

        QString values = QString::number(entity->mId());
        values.append(";");
        values.append(entity->values(";","",fields));

        QLocale::setDefault(defaultLocale);
        return values;
    }

    T* fromCSV(QString csvLine, QStringList fields)
    {
        QLocale defaultLocale;
        QLocale::setDefault(QLocale::C);

        T* t = new T();
        QObject *o = (QObject *)t;

        QStringList values = Utils::mySplit(csvLine,";",ESCAPE);

        for(int i=0; i<values.size(); i++) {
            QString value = values[i];
            value = Utils::unescape(value,";","");

            if(fields[i].toLower() == idColName().toLower()) {
                o->setProperty("mId",value);
            } else {
                o->setProperty(fields[i].toStdString().c_str(),value);
            }
        }

        QLocale::setDefault(defaultLocale);
        return t;
    }

    QString createCSV(QString where = QString()) {
        QString csv("");

        GenericListModel *list = findAll(where);
        QList<QObject *> entries = list->getEntries();

        if(!entries.isEmpty()) {
            Entity *e = (Entity *)entries[0];

            QString fields(idColName());
            fields.append(";");
            fields.append(e->keys(";"));

            csv.append(fields);
            for(int i=0; i<entries.length(); i++) {
                csv.append("\n");
                csv.append(toCSV((T *)entries[i]));
            }
        }

        delete list;

        return csv;
    }


    void importCSV(QString csv) {
        deleteAll();

        QStringList lines = csv.split("\n");
        QStringList fields = lines[0].split(";");

        for(int i=0; i<fields.length(); i++) {
            if(fields[i] == idColName()) {
                fields[i] = "mId";
                break;
            }
        }

        for(int i=1; i<lines.size(); i++) {
            if(lines[i].isEmpty()) continue;
            T *entity = fromCSV(lines[i],fields);
            save(entity,true);
            delete entity;
        }
    }

    Query getFindQuery() {
        return m_findQuery;
    }

    Query getCountQuery() {
        return m_countQuery;
    }

private:
    T* parseQuery(QSqlQuery query) {
        T* t = new T();
        QObject *o = (QObject *)t;

        QSqlRecord record = query.record();
        int numCols = record.count();

        for(int i=0; i<numCols; i++) {
            QString name = record.fieldName(i);
            QVariant value = query.value(i);

            if(name.toLower() == idColName().toLower()) {
                o->setProperty("mId",value);
            } else {
                o->setProperty(name.toStdString().c_str(),value);
            }
        }

        return t;
    };

    QList<QObject*> parseAll(QSqlQuery query) {
        QList<QObject*> list;

        while(query.next()) {
            T* t = parseQuery(query);
            list.append(t);
        }

        return list;
    }

    bool update(Entity* entity) {
        QString strQuery = "update " + m_tableName + " set " +  entity->keysValues() + " where " + idColName() + "=" + QString::number(entity->mId());

        QSqlQuery query;
        if(!query.exec(strQuery)) {
            qDebug("Query Error:");
            qDebug(query.lastError().text().toUtf8());
            return false;
        }

        return true;
    }

    QSqlDatabase m_db;
    QString m_tableName;
    int m_pageSize;
    Query m_findQuery;
    Query m_countQuery;
    QString m_idColName;
};

#define CUSTOM_DAO(className, tableName, pageSz, idColName, findQuery, countQuery) \
class className##DAO : public DAO<className> \
{ \
    public: \
        explicit className##DAO() : \
            DAO(tableName,pageSz,idColName,findQuery,countQuery) {}; \
    \
};

#endif // DAOT_H
